﻿using MiniExcelLibs;
using Sunny.UI;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PanhuaWin.工厂.仓库.报表
{
    public partial class Form原料卷库存状态 : UIPage
    {
        public Form原料卷库存状态()
        {
            InitializeComponent();
        }

        private void uiButton3_Click(object sender, EventArgs e)
        {
          DataTable dt = 查询原料卷();
            uiDataGridView1.DataSource = dt;
            uiDataGridView1.Refresh();
            string sql = "";
            sql = "select count(1) from Bus_热卷余额 where 状态 = '在库'";
           DataTable dt在库 = pub.GetDataTable(pub.ConnectionString,sql);
            sql = "select count(1) from Bus_热卷余额 where 状态 = '出库'";
            DataTable dt出库 = pub.GetDataTable(pub.ConnectionString, sql);

            uiTextBox总卷数.Text = dt.Rows.Count+"";
            uiTextBox在库数.Text = dt在库.Rows[0][0].ToString();
            uiTextBox出库数.Text = dt出库.Rows[0][0].ToString();

        }

        private DataTable 查询原料卷()
        {

            string sql = @"truncate TABLE Bus_热卷余额;
                                insert into Bus_热卷余额 
                             SELECT   dbo.Bus_StockInV.StockInType 单据类型, 
                                             dbo.Bus_StockInV.Creator 入库单制单人, dbo.Bus_StockInV.CreateDate 入库单制单日期, 
                
                                            ISNULL(dbo.Bus_StockInV.Approver, N'') AS  入库单审核人, dbo.Bus_StockInV.ApproverDate 入库单审核时间, dbo.Bus_StockInV.IsSendToNC 入库推送NC状态, 
                                            dbo.Bus_StockInV.SendToNCDate 入库推送NC时间,  dbo.Bus_StockInVolume.WarehouseCode 仓库编号, 
               
                                            dbo.Bus_StockInVolume.VolumeCode 原卷号, dbo.Bus_StockInVolume.VolumeCodeNew 热卷号, 
                                            dbo.Bus_StockInVolume.InventoryCode 存货编号, dbo.Bus_StockInVolume.Size 规格, dbo.Bus_StockInVolume.Thick 厚度, 
                                            dbo.Bus_StockInVolume.Width 宽度, dbo.Bus_StockInVolume.Material 材质, dbo.Bus_StockInVolume.Address 产地, 
                                            dbo.Bus_StockInVolume.Weight 重量,'在库'状态,'N'出库推送NC状态,'1991-01-01 14:42:49.890' 出库推送NC时间
				                            
                            FROM      dbo.Bus_StockInV INNER JOIN
                                            dbo.Bus_StockInVolume ON dbo.Bus_StockInV.guid = dbo.Bus_StockInVolume.guid 
                                            where Bus_StockInV.StockInType = '原料卷入库'";
				                          

                        if (string.IsNullOrWhiteSpace(uiTextBox原卷号.Text.Trim()) == false)
                        {
                            sql += " and Bus_StockInVolume.VolumeCode like '%" + uiTextBox原卷号.Text.Trim() + "%'";
                        }

                        if (string.IsNullOrWhiteSpace(uiTextBox热卷号.Text.Trim()) == false)
                        {
                            sql += " and Bus_StockInVolume.VolumeCodeNew like '%" + uiTextBox热卷号.Text.Trim() + "%'";
                        }

                        if (string.IsNullOrWhiteSpace(uiTextBox规格.Text.Trim()) == false)
                        {
                            sql += " and Bus_StockInVolume.Size like '%" + uiTextBox规格.Text.Trim() + "%'";
                        }
            sql += " order by dbo.Bus_StockInV.CreateDate; ";

            sql +=    @" update	Bus_热卷余额 set 状态 = '出库',出库推送NC状态 = Bus_StockOutVolume.IsSendToNC,出库推送NC时间 = Bus_StockOutVolume.SendToNCDate
                            from Bus_热卷余额
                            join Bus_StockOutVolume on Bus_StockOutVolume.VolumeCodeNew = Bus_热卷余额.热卷号;";
            int r = pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "select * from Bus_热卷余额";
            sql += "  order by 状态;";
            DataTable dt = pub.GetDataTable(pub.ConnectionString, sql);
            return dt;
        }

        private void uiButton2_Click(object sender, EventArgs e)
        {
            string path;
            this.saveFileDialog1.Filter = "Excel文件 (*.XLSX)|*.XLSX";
            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                path = this.saveFileDialog1.FileName;
                if (!File.Exists(path))
                {
                    MiniExcel.SaveAs(path, 查询原料卷(), true, "sheet1", ExcelType.XLSX);
                }
                else
                {
                    MessageBox.Show("文件已经存在,请重试!", "提示", MessageBoxButtons.OK);
                    return;
                }

            }
        }
    }
}
